import platform = require('../../../../platform/common/baseCommon');
import dbconnector = require('../../../../platform/server/dataService');
import db = require('../../../../platform/server/data');
import appServer = require('../../../../platform/server/appServer');

let gongshangService = new dbconnector.dataTableService();

gongshangService.serviceUrl = 'http://localhost:18080/ATSDT_SRV/HS/JsonRpc/SQLForNodeService';


//数据库连接信息
let connectInfo = new dbconnector.connectionInfo('gongshang', 'jdbc:oracle:thin:@localhost/ats60', 'ats50',
    'O6KMLUAIyuKp', 'oracle.jdbc.driver.OracleDriver');

// let connectInfo = new dbconnector.connectionInfo('gongshang', 'jdbc:oracle:thin:@19.129.247.1/orcl', 'fsgs_user',
// 'fsgs_user', 'oracle.jdbc.driver.OracleDriver');

gongshangService.dbConnectInfo = connectInfo;

gongshangService.sqlGenerator = new dbconnector.baseDmlStatementGenerator();

/**
 * 投资人信息
 */
let cmd_investor_info = new db.dataCommandInfo();
cmd_investor_info.id = 'cmd_investor_info';
cmd_investor_info.name = '投资人信息';
cmd_investor_info.sql = `select 
s.investorname as "投资人名称",
(case
    when s.certid is null then
     ' '
    else
     s.certid
  end) as "证照号码",
s.investnumber as "投资企业个数",
s.type_flag    as "投资人性质"
from (select t.investorname,
        '' as certid,
        count(t.investorname) as investnumber,
        t.type_flag
   from V_INVESTOR_GS t
  where t.type_flag = '企业法人'
  group by t.investorname, t.type_flag
 union
 select t.investorname,
        t.certid,
        count(t.investorname) as investnumber,
        t.type_flag
   from V_INVESTOR_GS t
  where t.type_flag = '自然人'
  group by t.investorname, t.certid, t.type_flag) s
where 1 = 1 and {0}`;
/** 数据过滤器 */
let filter_investor_info = new db.baseDataFilter()
    .and(
    new db.sqlFilter("INVESTORNAME", "s.INVESTORNAME like '%' || ? || '%' ", "投资人名称"),
    new db.sqlFilter("INVESTNUMBER", "s.INVESTNUMBER >= ? ", "投资企业个数")
    );
cmd_investor_info.dataFilter = [filter_investor_info];

/**
 * 关系图中级联的投资人信息
 */
let cmd_RG_investor_info = new db.dataCommandInfo();
cmd_RG_investor_info.id = 'cmd_RG_investor_info';
cmd_RG_investor_info.name = '关系图中级联的投资人信息';
cmd_RG_investor_info.sql = `select t.investorname as showName, (case
    when t.type_flag = '企业法人' then
     '企业'
    else
     t.type_flag
  end) as Investor_category, (case
    when t.type_flag = '企业法人' then
     ''
    else
    (case
        when t.certid is null then
         ' '
        else
         t.certid
      end)
  end) as certid,
  t.type_flag,
  t.corpname,
  '投资' as category,
  t.investorname  from V_INVESTOR_GS t
    where rownum = 1 and {0}`;
/** 数据过滤器 */
let filter_RG_investor_info = new db.baseDataFilter()
    .and(
    new db.baseDataFilter().or(
        new db.sqlFilter("TYPE_FLAG", `(case ?
                when '企业法人' then
                  1
                  when '自然人' then
                    0
                    end) = 1`, "投资人性质"),
        new db.sqlFilter("CERTID", `(case
            when t.CERTID is null then
             ' '
            else
             t.certid
          end) = ? `, "证照号码")
    ),
    new db.sqlFilter("INVESTORNAME", "t.INVESTORNAME = ? ", "投资人名称")
    );
cmd_RG_investor_info.dataFilter = [filter_RG_investor_info];

/**
 * 根据企业法人获取企业法人股东信息
 */
let cmd_RG_investor_info_by_enterprise = new db.dataCommandInfo();
cmd_RG_investor_info_by_enterprise.id = 'cmd_RG_investor_info_by_enterprise';
cmd_RG_investor_info_by_enterprise.name = '根据企业法人获取企业法人股东信息和其投资信息';
cmd_RG_investor_info_by_enterprise.sql = `select *
from (select t.investorname as showName,
             (case
               when t.type_flag = '企业法人' then
                '企业'
               else
                t.type_flag
             end) as Investor_category,
             (case
               when t.type_flag = '企业法人' then
                ''
               else
                (case
                  when t.certid is null then
                   ' '
                  else
                   t.certid
                end)
             end) as certid,
             t.type_flag,
             t.corpname,
             '投资' as category,
             t.investorname,
             t.corpname as QYFR
        from V_INVESTOR_GS t
      
      union
      
      select t.CORPNAME as showName,
             '企业' as Investor_category,
             t.type_flag,
             '' as certid,
             t.corpname,
             '被投资' as category,
             t.investorname,
             t.INVESTORNAME as QYFR
        from V_INVESTOR_GS t) t
where 1 = 1
 and {0}`;
/** 数据过滤器 */
let filter_RG_investor_info_by_enterprise = new db.baseDataFilter()
    .and(
    new db.sqlFilter("CORPNAME", "t.QYFR = ? ", "企业名称（即企业法人）")
    );
cmd_RG_investor_info_by_enterprise.dataFilter = [filter_RG_investor_info_by_enterprise];

/**
 * 根据自然人获取投资企业信息
 */
let cmd_RG_enterprise_info_by_person = new db.dataCommandInfo();
cmd_RG_enterprise_info_by_person.id = 'cmd_RG_enterprise_info_by_person';
cmd_RG_enterprise_info_by_person.name = '根据自然人获取投资企业信息';
cmd_RG_enterprise_info_by_person.sql = `select t.CORPNAME as showName,
'企业' as Investor_category,
t.type_flag,
t.corpname,
'被投资' as category,
t.investorname
from V_INVESTOR_GS t
    where 1=1 and {0}`;
/** 数据过滤器 */
let filter_RG_enterprise_info_by_person = new db.baseDataFilter()
    .and(
    new db.sqlFilter("CERTID", `(case
            when t.CERTID is null then
             ' '
            else
             t.certid
          end) = ? `, "证照号码"),
    new db.sqlFilter("INVESTORNAME", "t.INVESTORNAME = ? ", "投资人名称")
    );
cmd_RG_enterprise_info_by_person.dataFilter = [filter_RG_enterprise_info_by_person];

gongshangService.commandList = [
    cmd_investor_info
    , cmd_RG_investor_info
    , cmd_RG_investor_info_by_enterprise
    , cmd_RG_enterprise_info_by_person
];

//加到服务列表
platform.fetchAddonProtoType(gongshangService);
export let dataServiceInfo = new appServer.serviceInfo();
dataServiceInfo.serviceName = "gongshangService";
dataServiceInfo.serviceObject = gongshangService;







